﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Runda.CMS.Common.Utility;
using System.Data.Common;
using Runda.CMS.Common.Entity;

namespace Runda.CMS.Dal
{
    public partial class CtrtContractDal
    {
        public DataTable QueryContract(string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder("select * from (select A.*,B.CustomerName,C.Code as ContractAttributeName from Cms_Ctrt_Contract A JOIN Cms_Ctrt_Customer B ON A.CustomerID = B.CustomerID Left JOIN Cms_Sys_Dictionary C ON A.ContractAttribute = C.ID) AS Z where Deleted=0 {0}"); 
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

  
        public List<CtrtContractEntity> GetContractNotInGroup(Guid ContractID,string ContractCode)
        {
            List<CtrtContractEntity> entities = null;
            DbCommand dbcommand = CurrentDatabase.GetStoredProcCommand("Proc_GetContractGroup");

            CurrentDatabase.AddInParameter(dbcommand, "@ContractID", DbType.Guid,ContractID);
            CurrentDatabase.AddInParameter(dbcommand, "@ContractCode", DbType.String,ContractCode);

            using (IDataReader dr = CurrentDatabase.ExecuteReader(dbcommand))
            {
                entities = this.GetListFromReader<CtrtContractEntity>(dr);
            }

            return entities;
        }
    }
}
